<test>
  <sql>SELECT  t.TerritoryId,
        LTRIM(RTRIM(t.TerritoryDescription)) TerritoryDescription, 
        LTRIM(RTRIM((
                SELECT r.RegionDescription
                FROM Region r
                WHERE r.RegionID = t.RegionID
              ))) AS Region
FROM Territories t
</sql>
  <expectedResults>
    <NewDataSet>
  <xs:schema id="NewDataSet" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="Table1">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="TerritoryID" type="xs:string" minOccurs="0" />
                <xs:element name="TerritoryDescription" type="xs:string" minOccurs="0" />
                <xs:element name="Region" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <Table1>
    <TerritoryID>01581</TerritoryID>
    <TerritoryDescription>Westboro</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>01730</TerritoryID>
    <TerritoryDescription>Bedford</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>01833</TerritoryID>
    <TerritoryDescription>Georgetow</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>02116</TerritoryID>
    <TerritoryDescription>Boston</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>02139</TerritoryID>
    <TerritoryDescription>Cambridge</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>02184</TerritoryID>
    <TerritoryDescription>Braintree</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>02903</TerritoryID>
    <TerritoryDescription>Providence</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>03049</TerritoryID>
    <TerritoryDescription>Hollis</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>03801</TerritoryID>
    <TerritoryDescription>Portsmouth</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>06897</TerritoryID>
    <TerritoryDescription>Wilton</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>07960</TerritoryID>
    <TerritoryDescription>Morristown</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>08837</TerritoryID>
    <TerritoryDescription>Edison</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>10019</TerritoryID>
    <TerritoryDescription>New York</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>10038</TerritoryID>
    <TerritoryDescription>New York</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>11747</TerritoryID>
    <TerritoryDescription>Mellvile</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>14450</TerritoryID>
    <TerritoryDescription>Fairport</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>19428</TerritoryID>
    <TerritoryDescription>Philadelphia</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>19713</TerritoryID>
    <TerritoryDescription>Neward</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>20852</TerritoryID>
    <TerritoryDescription>Rockville</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>27403</TerritoryID>
    <TerritoryDescription>Greensboro</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>27511</TerritoryID>
    <TerritoryDescription>Cary</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>29202</TerritoryID>
    <TerritoryDescription>Columbia</TerritoryDescription>
    <Region>Southern</Region>
  </Table1>
  <Table1>
    <TerritoryID>30346</TerritoryID>
    <TerritoryDescription>Atlanta</TerritoryDescription>
    <Region>Southern</Region>
  </Table1>
  <Table1>
    <TerritoryID>31406</TerritoryID>
    <TerritoryDescription>Savannah</TerritoryDescription>
    <Region>Southern</Region>
  </Table1>
  <Table1>
    <TerritoryID>32859</TerritoryID>
    <TerritoryDescription>Orlando</TerritoryDescription>
    <Region>Southern</Region>
  </Table1>
  <Table1>
    <TerritoryID>33607</TerritoryID>
    <TerritoryDescription>Tampa</TerritoryDescription>
    <Region>Southern</Region>
  </Table1>
  <Table1>
    <TerritoryID>40222</TerritoryID>
    <TerritoryDescription>Louisville</TerritoryDescription>
    <Region>Eastern</Region>
  </Table1>
  <Table1>
    <TerritoryID>44122</TerritoryID>
    <TerritoryDescription>Beachwood</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>45839</TerritoryID>
    <TerritoryDescription>Findlay</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>48075</TerritoryID>
    <TerritoryDescription>Southfield</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>48084</TerritoryID>
    <TerritoryDescription>Troy</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>48304</TerritoryID>
    <TerritoryDescription>Bloomfield Hills</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>53404</TerritoryID>
    <TerritoryDescription>Racine</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>55113</TerritoryID>
    <TerritoryDescription>Roseville</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>55439</TerritoryID>
    <TerritoryDescription>Minneapolis</TerritoryDescription>
    <Region>Northern</Region>
  </Table1>
  <Table1>
    <TerritoryID>60179</TerritoryID>
    <TerritoryDescription>Hoffman Estates</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>60601</TerritoryID>
    <TerritoryDescription>Chicago</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>72716</TerritoryID>
    <TerritoryDescription>Bentonville</TerritoryDescription>
    <Region>Southern</Region>
  </Table1>
  <Table1>
    <TerritoryID>75234</TerritoryID>
    <TerritoryDescription>Dallas</TerritoryDescription>
    <Region>Southern</Region>
  </Table1>
  <Table1>
    <TerritoryID>78759</TerritoryID>
    <TerritoryDescription>Austin</TerritoryDescription>
    <Region>Southern</Region>
  </Table1>
  <Table1>
    <TerritoryID>80202</TerritoryID>
    <TerritoryDescription>Denver</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>80909</TerritoryID>
    <TerritoryDescription>Colorado Springs</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>85014</TerritoryID>
    <TerritoryDescription>Phoenix</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>85251</TerritoryID>
    <TerritoryDescription>Scottsdale</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>90405</TerritoryID>
    <TerritoryDescription>Santa Monica</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>94025</TerritoryID>
    <TerritoryDescription>Menlo Park</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>94105</TerritoryID>
    <TerritoryDescription>San Francisco</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>95008</TerritoryID>
    <TerritoryDescription>Campbell</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>95054</TerritoryID>
    <TerritoryDescription>Santa Clara</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>95060</TerritoryID>
    <TerritoryDescription>Santa Cruz</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>98004</TerritoryID>
    <TerritoryDescription>Bellevue</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>98052</TerritoryID>
    <TerritoryDescription>Redmond</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
  <Table1>
    <TerritoryID>98104</TerritoryID>
    <TerritoryDescription>Seattle</TerritoryDescription>
    <Region>Western</Region>
  </Table1>
</NewDataSet>
  </expectedResults>
  <expectedPlan>
    <executionPlan>
      <element operator="Select">
        <properties>
          <property name="Output List.[0]" value="t.TerritoryID AS TerritoryID" />
          <property name="Output List.[0].Table" value="t" />
          <property name="Output List.[0].Column" value="TerritoryID" />
          <property name="Output List.[0].DataType" value="String" />
          <property name="Output List.[0].Output Name" value="TerritoryID" />
          <property name="Output List.[1]" value="Expr1003 AS TerritoryDescription" />
          <property name="Output List.[1].Column" value="Expr1003" />
          <property name="Output List.[1].DataType" value="String" />
          <property name="Output List.[1].Output Name" value="TerritoryDescription" />
          <property name="Output List.[2]" value="Expr1008 AS Region" />
          <property name="Output List.[2].Column" value="Expr1008" />
          <property name="Output List.[2].DataType" value="String" />
          <property name="Output List.[2].Output Name" value="Region" />
        </properties>
        <input>
          <element operator="NestedLoops">
            <properties>
              <property name="Output List.[0]" value="t.TerritoryID" />
              <property name="Output List.[0].Table" value="t" />
              <property name="Output List.[0].Column" value="TerritoryID" />
              <property name="Output List.[0].DataType" value="String" />
              <property name="Output List.[1]" value="Expr1003" />
              <property name="Output List.[1].Column" value="Expr1003" />
              <property name="Output List.[1].DataType" value="String" />
              <property name="Output List.[2]" value="Expr1008" />
              <property name="Output List.[2].Column" value="Expr1008" />
              <property name="Output List.[2].DataType" value="String" />
              <property name="Logical Operator" value="Inner Join" />
              <property name="Statistics.Open Count" value="1" />
              <property name="Statistics.Row Count" value="53" />
              <property name="Statistics.Average Row Count" value="53" />
              <property name="Outer References.[0]" value="t.RegionID" />
              <property name="Outer References.[0].Table" value="t" />
              <property name="Outer References.[0].Column" value="RegionID" />
              <property name="Outer References.[0].DataType" value="Int32" />
            </properties>
            <input>
              <element operator="ComputeScalar">
                <properties>
                  <property name="Output List.[0]" value="t.TerritoryID" />
                  <property name="Output List.[0].Table" value="t" />
                  <property name="Output List.[0].Column" value="TerritoryID" />
                  <property name="Output List.[0].DataType" value="String" />
                  <property name="Output List.[1]" value="t.RegionID" />
                  <property name="Output List.[1].Table" value="t" />
                  <property name="Output List.[1].Column" value="RegionID" />
                  <property name="Output List.[1].DataType" value="Int32" />
                  <property name="Output List.[2]" value="Expr1003" />
                  <property name="Output List.[2].Column" value="Expr1003" />
                  <property name="Output List.[2].DataType" value="String" />
                  <property name="Statistics.Open Count" value="1" />
                  <property name="Statistics.Row Count" value="53" />
                  <property name="Statistics.Average Row Count" value="53" />
                  <property name="Defined Values.[0]" value="Expr1003 = LTRIM(RTRIM(t.TerritoryDescription))" />
                  <property name="Defined Values.[0].Target" value="Expr1003" />
                  <property name="Defined Values.[0].DataType" value="String" />
                  <property name="Defined Values.[0].Source" value="LTRIM(RTRIM(t.TerritoryDescription))" />
                </properties>
                <input>
                  <element operator="TableScan">
                    <properties>
                      <property name="Table" value="Territories AS t" />
                      <property name="Output List.[0]" value="t.TerritoryID" />
                      <property name="Output List.[0].Table" value="t" />
                      <property name="Output List.[0].Column" value="TerritoryID" />
                      <property name="Output List.[0].DataType" value="String" />
                      <property name="Output List.[1]" value="t.TerritoryDescription" />
                      <property name="Output List.[1].Table" value="t" />
                      <property name="Output List.[1].Column" value="TerritoryDescription" />
                      <property name="Output List.[1].DataType" value="String" />
                      <property name="Output List.[2]" value="t.RegionID" />
                      <property name="Output List.[2].Table" value="t" />
                      <property name="Output List.[2].Column" value="RegionID" />
                      <property name="Output List.[2].DataType" value="Int32" />
                      <property name="Statistics.Open Count" value="1" />
                      <property name="Statistics.Row Count" value="53" />
                      <property name="Statistics.Average Row Count" value="53" />
                    </properties>
                    <input />
                  </element>
                </input>
              </element>
              <element operator="Assert">
                <properties>
                  <property name="Output List.[0]" value="Expr1008" />
                  <property name="Output List.[0].Column" value="Expr1008" />
                  <property name="Output List.[0].DataType" value="String" />
                  <property name="Statistics.Open Count" value="53" />
                  <property name="Statistics.Row Count" value="53" />
                  <property name="Statistics.Average Row Count" value="1" />
                  <property name="Predicate" value="CASE WHEN Expr1006 &gt; 1 THEN 0 END" />
                </properties>
                <input>
                  <element operator="ComputeScalar">
                    <properties>
                      <property name="Output List.[0]" value="Expr1006" />
                      <property name="Output List.[0].Column" value="Expr1006" />
                      <property name="Output List.[0].DataType" value="Int32" />
                      <property name="Output List.[1]" value="Expr1008" />
                      <property name="Output List.[1].Column" value="Expr1008" />
                      <property name="Output List.[1].DataType" value="String" />
                      <property name="Statistics.Open Count" value="53" />
                      <property name="Statistics.Row Count" value="53" />
                      <property name="Statistics.Average Row Count" value="1" />
                      <property name="Defined Values.[0]" value="Expr1008 = LTRIM(RTRIM(Expr1007))" />
                      <property name="Defined Values.[0].Target" value="Expr1008" />
                      <property name="Defined Values.[0].DataType" value="String" />
                      <property name="Defined Values.[0].Source" value="LTRIM(RTRIM(Expr1007))" />
                    </properties>
                    <input>
                      <element operator="StreamAggregate">
                        <properties>
                          <property name="Output List.[0]" value="Expr1006" />
                          <property name="Output List.[0].Column" value="Expr1006" />
                          <property name="Output List.[0].DataType" value="Int32" />
                          <property name="Output List.[1]" value="Expr1007" />
                          <property name="Output List.[1].Column" value="Expr1007" />
                          <property name="Output List.[1].DataType" value="String" />
                          <property name="Statistics.Open Count" value="53" />
                          <property name="Statistics.Row Count" value="53" />
                          <property name="Statistics.Average Row Count" value="1" />
                          <property name="Defined Values.[0]" value="Expr1006 = COUNT(0)" />
                          <property name="Defined Values.[0].Target" value="Expr1006" />
                          <property name="Defined Values.[0].DataType" value="Int32" />
                          <property name="Defined Values.[0].Source" value="COUNT(0)" />
                          <property name="Defined Values.[1]" value="Expr1007 = ANY(r.RegionDescription)" />
                          <property name="Defined Values.[1].Target" value="Expr1007" />
                          <property name="Defined Values.[1].DataType" value="String" />
                          <property name="Defined Values.[1].Source" value="ANY(r.RegionDescription)" />
                        </properties>
                        <input>
                          <element operator="IndexSpool">
                            <properties>
                              <property name="Output List.[0]" value="r.RegionDescription" />
                              <property name="Output List.[0].Table" value="r" />
                              <property name="Output List.[0].Column" value="RegionDescription" />
                              <property name="Output List.[0].DataType" value="String" />
                              <property name="Statistics.Open Count" value="53" />
                              <property name="Statistics.Row Count" value="53" />
                              <property name="Statistics.Average Row Count" value="1" />
                              <property name="Logical Operator" value="Eager Spool" />
                              <property name="With Stack" value="False" />
                              <property name="Index" value="r.RegionID" />
                              <property name="Index.Table" value="r" />
                              <property name="Index.Column" value="RegionID" />
                              <property name="Index.DataType" value="Int32" />
                              <property name="Probe" value="t.RegionID" />
                            </properties>
                            <input>
                              <element operator="TableScan">
                                <properties>
                                  <property name="Table" value="Region AS r" />
                                  <property name="Output List.[0]" value="r.RegionID" />
                                  <property name="Output List.[0].Table" value="r" />
                                  <property name="Output List.[0].Column" value="RegionID" />
                                  <property name="Output List.[0].DataType" value="Int32" />
                                  <property name="Output List.[1]" value="r.RegionDescription" />
                                  <property name="Output List.[1].Table" value="r" />
                                  <property name="Output List.[1].Column" value="RegionDescription" />
                                  <property name="Output List.[1].DataType" value="String" />
                                  <property name="Statistics.Open Count" value="1" />
                                  <property name="Statistics.Row Count" value="4" />
                                  <property name="Statistics.Average Row Count" value="4" />
                                </properties>
                                <input />
                              </element>
                            </input>
                          </element>
                        </input>
                      </element>
                    </input>
                  </element>
                </input>
              </element>
            </input>
          </element>
        </input>
      </element>
    </executionPlan>
  </expectedPlan>
</test>